package com.demo.service.Impl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.demo.beans.Bean;
import com.demo.utils.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

@Service
public class FileService {
    public List<Bean> analysisFile(MultipartFile file) throws IOException {
        String originalFilename = file.getOriginalFilename();

        InputStream inputStream = file.getInputStream();
        Workbook workbook = new XSSFWorkbook(inputStream);
        int sheetCount = workbook.getNumberOfSheets();
        System.out.println("文档一共有"+sheetCount+"个Sheet");
        //遍历Sheet
        String year = null;
        String batchCode = null;
        String batch = null;
        String schoolCode = null;
        String schoolName = null;
        String branchCode = null; //科类代码
        String branch = null; //科类
        String major = null;
        Integer planNum = 0;
        Integer lowestScore = 0;
        Integer zdfwc = 0;
        List<Bean> beanList = new ArrayList<>();
        for(int i = 0;i < sheetCount;i++){
            System.out.println("开始遍历第"+i+"个sheet_________________________________________________________________________");
            //得到Sheet
            Sheet sheet = workbook.getSheetAt(i);
            //得到每个Sheet的行数,此工作表中包含的最后一行(Row的index是从0开始的)
            int rowCount = sheet.getLastRowNum();
//            System.out.println("第"+i+"个sheet中一共有"+rowCount+"行");
            //遍历Row
            for(int j = 0 ;j <= rowCount;j++){
//                System.out.println("开始遍历第"+j+"行~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~");
                //得到Row
                Row row = sheet.getRow(j);
                if(row == null){
                    System.out.println("第"+j+"行为空，跳出本行");
                    continue;
                }
                //得到每个Row的单元格数
                int cellCount = row.getLastCellNum();
//                System.out.println("第"+i+"个sheet中的第"+j+"行有"+cellCount+"个单元格");
                for(int k = 0 ;k < cellCount;k++){
//                    System.out.println("开始遍历第"+k+"个单元格+++++++");
                    try {
                        Cell cell = row.getCell(k);
                        //输出单元格里的值
                        if (j >= 1){
                            if (k == 0){
                                Double doubleYear = (Double) ExcelUtil.getCellFormatValue(cell);
                                String s = String.valueOf(doubleYear);
                                String[] split = s.split("\\.");
                                year = split[0];
                            }else if (k == 1){
//                                batchCode = (String) ExcelUtil.getCellFormatValue(cell);
                                batchCode = String.valueOf(ExcelUtil.getCellFormatValue(cell));
                            }else if (k == 2){
                                batch = String.valueOf(ExcelUtil.getCellFormatValue(cell));
                            }else if (k == 3){
                                Double code = (Double) ExcelUtil.getCellFormatValue(cell);
                                String s = String.valueOf(code);
                                String[] split = s.split("\\.");
                                schoolCode = split[0];
                            }else if (k == 4){
                                schoolName = (String) ExcelUtil.getCellFormatValue(cell);
                            }else if (k == 5){
                                branchCode = (String) ExcelUtil.getCellFormatValue(cell);
                            }else if (k == 6){
                                branch = (String) ExcelUtil.getCellFormatValue(cell);
                            }else if (k == 7){
                                major = (String) ExcelUtil.getCellFormatValue(cell);
                            }else if (k == 8){
                                if (ExcelUtil.getCellFormatValue(cell) != ""){
                                    Double aDouble = (Double)ExcelUtil.getCellFormatValue(cell);
                                    Integer integer = Integer.valueOf(aDouble.intValue());
                                    planNum = integer;
                                }
                            }else if (k == 9){
                                if (ExcelUtil.getCellFormatValue(cell) != "") {
                                    Double aDouble = (Double)ExcelUtil.getCellFormatValue(cell);
                                    Integer integer = Integer.valueOf(aDouble.intValue());
                                    lowestScore = integer;
                                }
                            }else if (k == 10){
                                if (ExcelUtil.getCellFormatValue(cell) != "") {
                                    Double aDouble = (Double)ExcelUtil.getCellFormatValue(cell);
                                    Integer integer = Integer.valueOf(aDouble.intValue());
                                    zdfwc = integer;
                                }
                                Bean bean = new Bean();
                                String id = UUID.randomUUID().toString();
                                id = id.substring(id.length()-17,id.length());
                                bean.setId(id);
                                bean.setBatchCode(batchCode);
                                bean.setBatch(batch);
                                bean.setSchoolCode(schoolCode);
                                bean.setSchoolName(schoolName);
                                bean.setBranchCode(branchCode);
                                bean.setBranch(branch);
                                bean.setMajor(major);
                                bean.setPlanNum(planNum);
                                bean.setYear(year);
                                bean.setLowestScore(lowestScore);
                                bean.setZdfwc(zdfwc);
                                bean.setIsNew(1);
                                beanList.add(bean);
                                planNum = 0;
                                lowestScore = 0;
                                zdfwc = 0;
                            }
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        System.out.println(j);
                        System.out.println(k);
                    }
//                    System.out.println(ExcelUtil.getCellFormatValue(cell));
                }
            }
        }
        return beanList;
    }


    public HSSFWorkbook outputExcel(String allDom, String queryCondition){
        List<Bean> beans = new ArrayList<Bean>();
        JSONArray objects = JSONObject.parseArray(allDom);
        String year = null;
        String batchCode = null;
        String batch = null;
        String schoolCode = null;
        String schoolName = null;
        String branchCode = null; //科类代码
        String branch = null; //科类
        String major = null;
        Integer planNum = 0;
        Integer lowestScore = 0;
        Integer zdfwc = 0;
        for (int i = 0; i < objects.size(); i++) {
            String beanStr = (String) objects.get(i);
            String[] split = beanStr.split("#");
            Bean bean = new Bean();
            for (int j = 0; j < split.length; j++) {
                switch (j){
                    case 0:
                        year = split[j];
                        if (!"*".equals(year)){
                            bean.setYear(year);
                        }
                        break;
                    case 1:
                        batch = split[j];
                        if (!"*".equals(batch)){
                            bean.setBatch(batch);
                        }
                        break;
                    case 2:
                        batchCode = split[j];
                        if (!"*".equals(batchCode)){
                            bean.setBatchCode(batchCode);
                        }
                        break;
                    case 3:
                        schoolName = split[j];
                        if (!"*".equals(schoolName)){
                            bean.setSchoolName(schoolName);
                        }
                        break;
                    case 4:
                        schoolCode = split[j];
                        if (!"*".equals(schoolCode)){
                            bean.setSchoolCode(schoolCode);
                        }
                        break;
                    case 5:
                        branch = split[j];
                        if (!"*".equals(branch)){
                            bean.setBranch(branch);
                        }
                        break;
                    case 6:
                        branchCode = split[j];
                        if (!"*".equals(branchCode)){
                            bean.setBranchCode(branchCode);
                        }
                        break;
                    case 7:
                        major = split[j];
                        if (!"*".equals(major)){
                            bean.setMajor(major);
                        }
                        break;
                    case 8:
                        planNum = Integer.valueOf(split[j]);
                        if (!"*".equals(planNum)){
                            bean.setPlanNum(planNum);
                        }
                        break;
                    case 9:
                        lowestScore = Integer.valueOf(split[j]);
                        if (!"*".equals(lowestScore)){
                            bean.setLowestScore(lowestScore);
                        }
                        break;
                    case 10:
                        zdfwc = Integer.valueOf(split[j]);
                        if (!"*".equals(zdfwc)){
                            bean.setZdfwc(zdfwc);
                        }
                        break;
                }
            }
            beans.add(bean);
        }
        HSSFWorkbook workbook = ExcelUtil.makeExcel(beans,queryCondition);
        return workbook;
    }

    public HSSFWorkbook outputExcel(List<Bean> beans){
        HSSFWorkbook workbook = ExcelUtil.makeExcel(beans,"1");
        return workbook;
    }
}
